## 6.8. Extended Functionality

Both MySQL and mSQL have a few quirky extensions that do not really have counterparts in the other database engine. Most of MySQL's extensions are generally in line with the ANSI SQL standard. mSQL's extensions are simply related to special variables you can access while working with an mSQL database.

### 6.8.1. MySQL Features

MySQL goes well beyond mSQL's support for SQL by supporting functions and a limited concept of outer joins. Functions in SQL are similar to functions in other programming languages like C and Perl. The function takes zero or more arguments and returns some value. For example, the function `SQRT(16)` returns 4. Within a MySQL `SELECT` statement, functions may be used in either of two places:

As a value to be retrieved

  This form involves a function in the place of a column in the list of columns to be retrieved. The return value of the function, evaluated for each selected row, is part of the returned result set as if it were a column in the database. For example:

>     # Select the name of each event as well as the date of the event
>     # formatted in a human-readable form for all events more
>     # recent than the given time. The FROM_UnixTIME() function
>     # transforms a standard Unix time value into a human
>     # readable form.
>     SELECT name, FROM_UnixTIME(date)
>     FROM events
>     WHERE time > 90534323

>     # Select the title of a paper, the full text of the paper,
>     # and the length (in bytes) of the full text for all
>     # papers authored by Stacie Sheldon.
>     # The LENGTH() function returns the character length of
>     # a given string.
>     SELECT title, text, LENGTH(text)
>     FROM papers
>     WHERE author = 'Stacie Sheldon'

As part of a WHERE clause

  This form involves a function in the place of a constant when evaluating a `WHERE` clause. The value of the function is used for comparison for each row of the table. For example:

>     # Randomly select the name of an entry from a pool of 35
>     # entries. The RAND() function generates a random number
>     # between 0 and 1 (multiplied by 34 to make it between 0
>     # and 34 and incremented by 1 to make it between 1 and
>     # 35). The ROUND() function returns the given number
>     # rounded to the nearest integer, resulting in a whole
>     # number between 1 and 35, which should match one of
>     # the ID numbers in the table.
>     SELECT name
>     FROM entries
>     WHERE id = ROUND( (RAND()*34) + 1 )

>     # You may use functions in both the value list and the
>     # WHERE clause. This example selects the name and date
>     # of each event less than a day old. The UNIX_TIMESTAMP()
>     # function, with no arguments, returns the current time
>     # in Unix format.
>     SELECT name, FROM_UnixTIME(date)
>     FROM events
>     WHERE time > (Unix_TIMESTAMP() - (60 * 60 * 24) )

>     # You may also use the value of a table field within
>     # a function. This example returns the name of anyone
>     # who used their name as their password. The ENCRYPT()
>     # function returns a Unix password-style encryption
>     # of the given string using the supplied 2-character salt.
>     # The LEFT() function returns the left-most n characters
>     # of the given string.
>     SELECT name
>     FROM people
>     WHERE password = ENCRYPT(name, LEFT(name, 2))

Finally, MySQL supports a more powerful joining than the simple inner joins we have used so far. Specifically, MySQL supports something called a left outer join (also known as simply outer join). This type of join is similar to an inner join, except that it includes data in the first column named that does not match any in the second column. If you remember our author and book tables from earlier in the chapter, you will remember that our join would not list any authors who did not have a book in our database. It is common that you may want to show entries from one table that have no corresponding data in the table to which you are joining. That is where an outer join comes into play:

>     SELECT book.title, author.name
>     FROM author
>     LEFT JOIN book ON book.author = author.id

Note that a outer join uses the keyword `ON` instead of `WHERE`. The results of our query would look like this:

>     +----------------+----------------+
>     | book.title     | author.name    |
>     +----------------+----------------+
>     | The Green Mile | Stephen King   |
>     | Guards, Guards!| Terry Pratchett|
>     | Imzadi         | Peter David    |
>     | Gold           | Isaac Asimov   |
>     | Howling Mad    | Peter David    |
>     | NULL           | Neil Gaiman    |
>     +----------------+----------------+

MySQL takes this concept one step further through the use of a natural outer join. A natural outer join will combine the rows from two tables where the two tables have identical column names with identical types and the values in those columns are identical:

>     SELECT my_prod.name
>     FROM my_prod
>     NATURAL LEFT JOIN their_prod

### 6.8.2. mSQL Features

mSQL has five "system variables" that you can include in any query. We have already covered one of those values, `_seq`. The others are:

_rowid

  A unique identifier corresponding to a row of data being returned. You can use it with subsequent `UPDATE` or `DELETE` statements to improve efficiency. This approach, however, is definitely not recommended because multiple clients can mess each other up. Specifically, two clients can select the same row. The first one deletes it and then a third client inserts a new row. The new row can have the same `_rowid` value as the deleted row. If the second client then tries to update or delete using that `_rowid`, it will affect data it did not intend to affect.

_timestamp

  The time when the row in question was last modified. Under the current version of mSQL, this value is in the standard Unix time format. This behavior may change in future versions, so you should only use this value to compare with timestamps in other rows.

_sysdate

  Returns the current time on the mSQL server. This time can be used to ensure that all times used in the database are synchronized even if the clients are on systems with varying times. This time is given in the standard Unix format and will be the same no matter which table you select it from.

_user

  This value holds the name of the user for the current client connection. As with `_sysdate`, this value is not dependent on the table from which you choose to select it.

Copyright © 2001 O'Reilly & Associates. All rights reserved.